// 数据库执行 并返回数据
const { exMysql } = require('./mysql/mysql')

// 查询当前用户的数据
const selectUserInfo = (data) => {
    const promise = new Promise(async (resolve, reject) => {
        try {
            const name = data.name
            const sql = `SELECT * FROM user WHERE name='${name}';`
            let res = await exMysql(sql)
            resolve(res)

        } catch (error) {
            console.log('出错了---->',error);
            reject('出错了')
            return
        }
    })
    return promise
}


// 查询当前用户的记录 消费记录 和 收入记录
const selectRecrord = (id,type,size,page) => {
    console.log('size,page---->',size,page);
    const promise = new Promise(async (resolve, reject) => {
        try{
            let sql
            if(type == 'incall'){
                 sql = `SELECT * FROM record WHERE callid='${id}' ORDER BY incalldate DESC limit ${size} offset ${ size * (page -1)};`
            }else{
                sql = `SELECT * FROM record WHERE callid='${id}' ORDER BY outcalldate DESC limit ${size} offset ${size *  (page -1) };`
            }
            let res = await exMysql(sql)
            console.log('res---->',res);
            resolve(res)
        } catch (error){
            console.log('出错了---->',error);

            reject('出错了')
            return
        }
    })
    return promise
}

// 查询分页数据总数
const totalRecord = (id,type) => {
    const promise = new Promise(async (resolve, reject) => {
        try{
            let sql
            if(type == 'incall'){
                 sql = `SELECT COUNT(*) FROM record WHERE callid='${id}' and incall > 0;`
            }else{
                sql = `SELECT COUNT(*) FROM record WHERE callid='${id}' and outcall > 0;`
            }
            let res = await exMysql(sql)
            resolve(res)
        } catch (error){
            console.log('出错了---->',error);
            reject('出错了')
            return
        }
    })
    return promise
}




// 新增收入记录
const addIncallRecrord = (data) => {
    // console.log('data---->',data);
    const promise = new Promise(async (resolve, reject) => {
        try{
              // 请求数据库 拿到allincall 字段最大值
            const maxAllIncall = `SELECT MAX(allincall) from record;`
            let maxAllIncallNumber = await exMysql(maxAllIncall)

            // 当前 增加金额 加上当前总收入金额最大值  即为当前最新的所有总收入  
            let newAllIncall = Number(maxAllIncallNumber[0]["MAX(allincall)"]) + Number(data.moeny)

            const sql = `INSERT INTO record (callid,username,currentmoeny,allincall,incall,incalltype,incalldate,descriable,date) VALUES ('${data.id}','${data.name}','3400','${newAllIncall}','${data.moeny}','${data.incalltype}','${data.incalldate}','${data.textarea}','${data.day}');`
            let res = await exMysql(sql)
            resolve(res)
        } catch (error){
            console.log('出错了---->',error);
            reject('出错了')
            return
        }
    })
    return promise
}

// 新增支出记录
const addOutRecrord = (data) => {
    console.log('data---->',data);
    const promise = new Promise(async (resolve, reject) => {
        try{
            // 请求数据库 拿到alloutcall 字段最大值
            const maxAllOutcall = `SELECT MAX(alloutcall) from record;`
            let maxAllOutcallNumber = await exMysql(maxAllOutcall)
            // 当前 增加金额 加上当前总支出金额最大值  即为当前最新的所有总支出
            let newAllOutcall = Number(maxAllOutcallNumber[0]["MAX(alloutcall)"]) + Number(data.moeny)

            const sql = `INSERT INTO record (callid,username,currentmoeny,alloutcall,outcall,outcalltype,outcalldate,descriable,date) VALUES ('${data.id}','${data.name}','3400','${newAllOutcall}','${data.moeny}','${data.incalltype}','${data.incalldate}','${data.textarea}','${data.day}');`
            let res = await exMysql(sql)
            resolve(res)
        } catch (error){
            console.log('出错了---->',error);
            reject('出错了')
            return
        }
    })
    return promise
}


// 总收入计算
const allincall = (id) => {
    const promise = new Promise(async (resolve, reject) => {
        try{
            const sql = `SELECT SUM(incall) FROM record where callid='${id}';`
            let res = await exMysql(sql)
            resolve(res)
        } catch (error){
            console.log('出错了---->',error);
            reject('出错了')
            return
        }
    })
    return promise
}


// 总支出计算
const alloutcall = (id)=>{
    const promise = new Promise(async (resolve, reject) => {
        try{
            const sql = `SELECT SUM(outcall) FROM record where callid='${id}';`
            let res = await exMysql(sql)
            resolve(res)
        } catch (error){
            console.log('出错了---->',error);
            reject('出错了')
            return
        }
    })
    return promise
}

// 查询当前incall 和 outcall 最大值
const maxCall = (id) => {
    const promise = new Promise(async (resolve, reject) => {
        try{
            const sql1 = `SELECT MAX(allincall) FROM record where callid='${id}';`
            const sql2 = `SELECT MAX(alloutcall) FROM record where callid='${id}';`
            let res1 = await exMysql(sql1)
            let res2 = await exMysql(sql2)
            let result = {res1,res2 }
            console.log('result---->',result);
            resolve(result)
        } catch (error){
            console.log('出错了---->',error);
            reject('出错了')
            return
        }
    })
    return promise
}

// echars 数据查询 收入数据查询
const incallDataEch = (id) => {
    const promise = new Promise(async (resolve, reject) => {
        try{
            const sql = `SELECT incall,date,incalltype,incalldate from record WHERE callid='${id}' and incall > 0;`
            let res = await exMysql(sql)
            resolve(res)
        } catch (error){
            console.log('出错了---->',error);
            reject('出错了')
            return
        }
    })
    return promise
}

// echars 数据查询 支出数据查询
const outcallDataEch = (id) => {
    const promise = new Promise(async (resolve, reject) => {
        try{
            const sql = `SELECT outcall,date,outcalltype,outcalldate from record WHERE callid='${id}' and outcall > 0;`
            let res = await exMysql(sql)
            console.log('res---->',res);
            resolve(res)
        } catch (error){
            console.log('出错了---->',error);
            reject('出错了')
            return
        }
    })
    return promise
}

// 查询types    
const types = () => {
    const promise = new Promise(async (resolve, reject) => {
        try{
            const sql = `SELECT * from sys_dict;`
            let res = await exMysql(sql)
            resolve(res)
        } catch (error){
            console.log('出错了---->',error);
            reject('出错了')
            return
        }
    })
    return promise
}


module.exports = {
    selectUserInfo, selectRecrord, addIncallRecrord,addOutRecrord,allincall,alloutcall,maxCall,
    incallDataEch,types,outcallDataEch,totalRecord
}